using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Setup : DataAccess
    {
        public Setup(string db_str)
        {
            CreateConnection(db_str);
        }
        public DataSet GetCalendar(DateTime trans_date, string status)
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Calendar WHERE trans_date = @trans_date";
                cmd.Parameters.Add("@trans_date", SqlDbType.DateTime).Value = trans_date;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }

        public DataSet GetDataByProcedure(string sql_procedure, string sql_parameter)
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql_procedure + " " + sql_parameter;                
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }
        public object ModifyByProcedure(string sql_procedure, string sql_parameter)
        {
            object accounts = new object();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql_procedure + " " + sql_parameter;
                conn.Open();
                accounts = cmd.ExecuteScalar();
                if (accounts == null)
                    accounts = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return accounts;
        }

        public DataSet GetCompStruct()
        {
            DataSet tree = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM CompStructTree";                
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tree);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tree;
        }

        public DataSet GetSystemParameters()
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_SystemParameters";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }

        public DataSet GetReportParameters(string report_code)
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ReportControls";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@report_code", SqlDbType.VarChar, 4).Value = report_code;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }

        public DataSet GetReportSource(string report_code, DateTime from_date, DateTime to_date
            ,string board_id, string symbol, string account_type, string contract_type
            , string account_no, string bank_account)
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ReportSource";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = Int32.MaxValue;
                cmd.Parameters.Add("@report_code", SqlDbType.VarChar, 4).Value = report_code;
                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 20).Value = account_no;
                cmd.Parameters.Add("@from_date", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@to_date", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@account_type", SqlDbType.VarChar, 50).Value = account_type;
                cmd.Parameters.Add("@contract_type", SqlDbType.VarChar, 50).Value = contract_type;
                cmd.Parameters.Add("@bank_account", SqlDbType.VarChar, 50).Value = bank_account;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }

        public DataSet GetTranType(string module)
        {
            DataSet tran_type = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM TranType WHERE module = @module or @module = ''";
                cmd.Parameters.Add("@module", SqlDbType.VarChar, 2).Value = module;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tran_type);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tran_type;
        }

        public DataSet GetOptionList(int OptGrp)
        {
            DataSet tran_type = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ComboBoxData";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@OptGrp", SqlDbType.Int).Value = OptGrp;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tran_type);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tran_type;
        }

        public DataSet GetClientType()
        {
            DataSet tran_type = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ClientType";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tran_type);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tran_type;
        }

        public DataSet GetReportInitial()
        {
            DataSet tran_type = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ReportInitial";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tran_type);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tran_type;
        }

        public DataSet GetFee(string fee_id)
        {
            DataSet tran_type = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Fee WHERE feeid = @FeeId or @FeeId = ''";
                cmd.Parameters.Add("@FeeId", SqlDbType.VarChar, 2).Value = fee_id;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(tran_type);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return tran_type;
        }
        public void CreateFee(string fee_id, string description, double rate,
            decimal min_value, decimal max_value, byte scope, string tran_type, 
            DateTime date_modified, string modified_by)
        {

            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ins_Fee";
                cmd.Parameters.Add("@FeeId", SqlDbType.VarChar, 2).Value = fee_id;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 255).Value = description;
                cmd.Parameters.Add("@Rate", SqlDbType.Float).Value = rate;
                cmd.Parameters.Add("@MinValue", SqlDbType.Decimal).Value = min_value;
                cmd.Parameters.Add("@MaxValue", SqlDbType.Decimal).Value = max_value;
                cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar, 50).Value = modified_by;
                cmd.Parameters.Add("@DateModified", SqlDbType.DateTime).Value = date_modified;
                cmd.Parameters.Add("@Scope", SqlDbType.TinyInt).Value = scope;
                cmd.Parameters.Add("@TranType", SqlDbType.VarChar, 1).Value = tran_type;
                conn.Open();
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

        }
        public void CreateCalendar(DateTime trans_date, DateTime settle_date, string status,
            DateTime t1_date, DateTime t2_date)
        {

            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ins_Calendar";
                
                cmd.Parameters.Add("@status", SqlDbType.VarChar, 1).Value = status;
                cmd.Parameters.Add("@TRANS_DATE", SqlDbType.DateTime).Value = trans_date;
                cmd.Parameters.Add("@SETTLE_DATE", SqlDbType.DateTime).Value = settle_date;
                cmd.Parameters.Add("@T1_DATE", SqlDbType.DateTime).Value = t1_date;
                cmd.Parameters.Add("@T2_DATE", SqlDbType.DateTime).Value = t2_date;
                conn.Open();
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

        }
    }
}
